package zxj.spay.modular.data.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import zxj.spay.entity.Stream;

public interface StreamDao extends JpaRepository<Stream, Integer>{

	/**
	 * 查找一个用户，指定时间，指定状态的总金额
	 * @param user_id 用户id
	 * @param status  状态，0 收入，1 支出
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @return
	 */
	@Query("select sum(stream.money) as summoney from Stream stream where stream.user.id=?1 and stream.type.status=?2 and stream.useTime>=?3 and stream.useTime<=?4")
	Integer getIndexTotalByTimeAndUserId(int user_id,int status,Date startTime,Date endTime);

	/**
	 * 查找一个用户，指定时间，对于每项支出类型，分别支出的总金额
	 * @param user_id 用户id
	 * @param start 开始时间
	 * @param end   结束时间
	 * @return
	 */
	@Query("select sum(stream.money) as money,stream.type.typeName as typeName from Stream stream where user.id=?1 and stream.type.status=1 and stream.useTime>=?2 and stream.useTime<=?3 group by stream.type.id")
	List<Map<String,Object>> getCostTotal(int user_id, Date start, Date end);

	/**
	 * 查找一个用户，指定时间里，每天的支出总额
	 * @param user_id 用户id
	 * @param start 开始时间
	 * @param end 结束时间
	 * @return
	 */
	@Query("select sum(stream.money) as money,stream.useTime as useTime from Stream stream where stream.user.id=?1 and stream.type.status=?2 and stream.useTime>=?3 and stream.useTime<=?4 group by stream.useTime order by useTime asc")
	List<Map<String, Object>> getCostDayTotal(int user_id,int status,Date start, Date end);

}
